﻿using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml.Linq;

namespace LPBToolsLib
{
    /*
    AppSetting.json格式

    "SQLSerCFG": {
		"Address": "192.168.0.3",
		"DBUser": "MESProxy",
		"DBPass": "MESProxy",
		"DBName": "LDSys"
	},
     
     */
    public interface IFSQLConfig
    {
        [DisplayName("服务地址")]
        public string Address { get;  }
        [DisplayName("登陆用户")]
        public string DBUser { get; }
        [DisplayName("登陆密码")]
        public string DBPass { get;  }
        [DisplayName("连接初始数据库名称")]
        public string DBName { get; }
        [DisplayName("连接端口")]
        public int DBPort { get; }
        [DisplayName("连接字符串[SqlConnection]")]
        public string ConnectStr { get; }
    }

    public class SQLSerConfig : IFSQLConfig
    {
        protected JSONINISettings BaseSetCFG { get; set; } = null;
        public string Segment { get; private set; } = "";
        public SQLSerConfig(JSONINISettings tBaseSetCFG, string DestSegment = "")
        {
            BaseSetCFG = tBaseSetCFG;
            if (string.IsNullOrEmpty(DestSegment))
                Segment = "SQLServer";
            else
                Segment = DestSegment;
        }

        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "Address", "192.168.0.1");
                return "192.168.0.1";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "Address", value); }
        }


        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBUser", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBUser", value); }
        }

        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBPass", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPass", value); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBName", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<int>(Segment, "DBPort", 1433);
                return 1433;
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPort", value); }
        }

        [DisplayName("SQLSer连接字符串[SqlConnection]")]
        public virtual string ConnectStr
        {
            get
            {
                string sMask = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}";// ;TrustServerCertificate = true; MultipleActiveResultSets=true; 
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass);
            }
        }
    }

    public class SQLSerConfigS : JSONINISettings, IFSQLConfig
    {
        public string Segment { get; private set; } = "";
        public SQLSerConfigS(string DestFileName, string DestSegment="") : base(DestFileName)
        {
            if (string.IsNullOrEmpty(DestSegment))
                Segment = "SQLServer";
            else
                Segment = DestSegment;
        }

        [DisplayName("服务地址")]
        public string Address
        {
            get { return getCFGValue<string>(Segment, "Address", "192.168.0.1"); }
            set { setCFGValue(Segment, "Address", value); }
        }


        [DisplayName("登陆用户")]
        public string DBUser
        {
            get { return getCFGValue<string>(Segment, "DBUser", ""); }
            set { setCFGValue(Segment, "DBUser", value); }
        }

        [DisplayName("登陆密码")]
        public string DBPass
        {
            get { return getCFGValue<string>(Segment, "DBPass", ""); }
            set { setCFGValue(Segment, "DBPass", value); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get { return getCFGValue<string>(Segment, "DBName", ""); }
            set { setCFGValue(Segment, "DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get { return getCFGValue<int>(Segment, "DBPort", 1433); }
            set { setCFGValue(Segment, "DBPort", value); }
        }

        [DisplayName("SQLSer连接字符串[SqlConnection]")]
        public string ConnectStr
        {
            get
            {
                string sMask = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}";// ;TrustServerCertificate = true; MultipleActiveResultSets=true; 
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass);
            }
        }
    }

    public class SQLSerConfigR: IFSQLConfig
    {
        private IConfiguration CFGSection { get; set; } = null;
        [DisplayName("SQLSer服务地址")]
        public string Address
        {
            get
            {
                string sTmp = CFGSection?.GetSection("Address").Value;
                return (sTmp != null) ? sTmp : "192.168.0.1";
            }
        }
        [DisplayName("SQLSer登陆用户")]
        public string DBUser
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBUser").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("SQLSer登陆密码")]
        public string DBPass
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBPass").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("SQLSer中连接初始数据库名称")]
        public string DBName
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBName").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("连接端口")]
        public int DBPort { get { return 1433; } }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                /*
                 在Linux使用.netcore 3.1 连接 Windows 安装的SqlServer2008R2时报了很奇怪的错误
                 System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 35 - An internal exception was caught)

                网上查资料，网友说可能是sqlserver版本问题，需要打补丁

    SqlServer2008R2 需要安装补丁包SP3（已验证）链接
                https://www.microsoft.com/zh-CN/download/details.aspx?id=44271
    SqlServer2008需要安装补丁包SP4 链接
                https://www.microsoft.com/zh-CN/download/details.aspx?id=44278

SqlServer2012及更高版本可能没有这个问题。

我这里是SqlServer2008R2安装SP3补丁包后，就可以连接数据库正常了。
————————————————
版权声明：本文为CSDN博主「Robinson-sir」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zbc415766331/article/details/104699873



                是TLS 不兼容的原因，要修改

1. sudo vim /etc/ssl/openssl.cnf

2.文件末尾追加：

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=1

修改后要重启才能生效，至此访问数据的页面可以正常打开
————————————————
版权声明：本文为CSDN博主「li_rt4」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/li_rt4/article/details/122771163

                 */
                string sMask = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}";// ;TrustServerCertificate = true; MultipleActiveResultSets=true; 
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass);
            }
        }

        public SQLSerConfigR(IConfiguration DestCFGSection)
        {
            CFGSection = DestCFGSection;
        }
    }

    public class SQLSerHelper
    {
        public Recorder_Error SysErr { get; set; } = null;
        public IFSQLConfig Config { get; private set; } = null;
        public SQLSerHelper(IFSQLConfig DestConfig)
        {
            Config = DestConfig;
        }
        public SQLSerHelper(IFSQLConfig DestConfig, Recorder_Error DestSysErr)
        {
            Config = DestConfig;
            SysErr = DestSysErr;
        }

        public bool IsSerIPReady
        {
            get { return (Config != null) && BaseTools.PingTest(Config.Address); }
        }

        #region SQL语句执行
        /// <summary>
        /// 执行SQL查询Select语句，同时获得结果数量
        /// </summary>
        /// <param name="DestSql">需要执行的目标SQL，必须包括Select</param>
        /// <param name="recCount">返回结果的数量</param>
        /// <param name="DestSql">需要执行的目标SQL，必须包括Select</param>
        /// <param name="recCount">返回结果的数量</param>
        /// <param name="PageIdx">查询返回数据的起始页，从1开始</param>
        /// <param name="PageCount">查询返回数据每页记录数</param>
        /// <returns>返回的数据接口中包括1个数据结果表</returns>
        public DataTable runSelect(string DestSql, out int recCount,
            int PageIdx = 0, int PageCount = 0)
        {
            recCount = 0;
            DataSet tDataset = new DataSet();
            DataTable tDTable = null;
            if ((!string.IsNullOrEmpty(DestSql)) && (Config != null)
                && (DestSql.ToLower().IndexOf("select") >= 0))
                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlDataAdapter tAdapter = new SqlDataAdapter();
                            tAdapter.SelectCommand = new SqlCommand(DestSql, tDBCon);
                            string sResultTBName = "SelectResult";
                            if (PageCount > 0)
                            {
                                if (PageIdx <= 0)
                                    PageIdx = 1;
                                // srcTable:导入DataSet中的表名称，可以是任何名字，但不能为空
                                tAdapter.Fill(tDataset, (PageIdx - 1) * PageCount, PageCount, sResultTBName);
                            }
                            else
                            {
                                tAdapter.Fill(tDataset, sResultTBName);
                            }
                            tDTable = tDataset.Tables[sResultTBName];
                            recCount = tDTable.Rows.Count;
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr!=null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return tDTable;
        }

        /// <summary>
        /// 执行查询，并返回由查询返回的结果集中的第一行的第一列。 其他列或行将被忽略。
        /// </summary>
        /// <param name="DestSql"></param>
        /// <returns>结果集中第一行的第一列或 null 引用（如果结果集为空）</returns>
        public object runSelect(string DestSql)
        {
            string sSQL = DestSql;
            if ((!string.IsNullOrEmpty(DestSql)) && (Config != null)
                && (DestSql.ToLower().IndexOf("select") >= 0))
                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlCommand tDBCommand = new SqlCommand(DestSql, tDBCon);
                            return tDBCommand.ExecuteScalar();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return "";
        }

        /// <summary>
        /// Update操作数据
        /// </summary>
        /// <param name="updateSQL">等待执行的Update语句</param>
        /// <returns>返回影响的记录数量</returns>
        public int updateValues(string updateSQL)
        {
            if ((!string.IsNullOrEmpty(updateSQL)) && (Config != null)
                && ((updateSQL.ToLower().IndexOf("update") >= 0) || (updateSQL.ToLower().IndexOf("insert") >= 0)))

                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlCommand tCommand = new SqlCommand(updateSQL, tDBCon);
                            return tCommand.ExecuteNonQuery();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return -1;
        }

        #endregion
    }
}
